<?php
/**
 * 情報誌No検索用SQL作成クラス<br />
 *
 * @package apamanshop
 * @author SS鈴木
 * @copyright
 */
class Rstobi_Model_JyohoshinoSearch extends Rstobi_Model_Base
{
	/**
	 * 情報誌No検索用SQL取得
	 *
	 * @access	public
	 * @param   array
	 * @param	array ※参照渡し
	 * @return  string
	 */
	public function getJyohoshinosearch($params,&$db_params)
	{
		$sql = " FROM ";
		$sql .="     (SELECT";
		$sql .="              *";
		$sql .="          FROM";
		$sql .="              T03WEB_HEYA";
		$sql .="              ,T77ROOM_SEARCHNO";
		$sql .="          WHERE";
		$sql .="              T77_HAN_CD = '".substr($params['SchNo'],0,2)."'";
		$sql .="              AND T77_ROOM_SEARCH_NO = '".substr($params['SchNo'],2,5)."'";
		$sql .="              AND T03_KASHIDASHI_KUBUN = T77_KASHIDASHI_KUBUN";
		$sql .="              AND (";
		$sql .="                  T03_ORIGINAL_HEYA_CD01 = T77_ORIGNAL_TATEMONO_CD01 || '-' || SUBSTR(T77_ORIGNAL_HEYA_BANGO, 1, LENGTH(T03_HEYA_BANGOU))";
		$sql .="                  OR T03_ORIGINAL_HEYA_CD02 = T77_ORIGNAL_TATEMONO_CD01 || '-' || SUBSTR(T77_ORIGNAL_HEYA_BANGO, 1, LENGTH(T03_HEYA_BANGOU))";
		$sql .="                  OR T03_ORIGINAL_HEYA_CD01 = SUBSTR(T77_ORIGNAL_HEYA_CD, 1, LENGTH(T03_ORIGINAL_HEYA_CD01))";
		$sql .="              )";
		$sql .="              AND T03_TENPO_CD = T77_TENPO_CD";
		$sql .="           )T03";
		$sql .="        )T03_HEYA";
		$sql .="     ) T03 ";

		return $sql;
	}
}
?>